package cn.edu.buaa.treehole.dao.implement;

import cn.edu.buaa.treehole.dao.PostDao;
import cn.edu.buaa.treehole.dao.exception.DaoException;
import cn.edu.buaa.treehole.dao.exception.PidHasExistException;
import cn.edu.buaa.treehole.dao.exception.PidNotExistException;
import cn.edu.buaa.treehole.dao.util.DbAccess;
import cn.edu.buaa.treehole.dao.util.DbConnection;
import cn.edu.buaa.treehole.pojo.PostInfo;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

/*
 * @author: Zhu Leyan
 * */
@Repository
public class PostDaoImpl implements PostDao {

    private final DbConnection conn;

    private static final String insert = "INSERT INTO post (pid, title, content, signature, date) " +
            "VALUES (%d, \"%s\", \"%s\", \"%s\", \"%s\")";

    private static final String selectPid = "SELECT * FROM post WHERE pid=%d";

    private static final String deletePid = "DELETE FROM post WHERE pid=%d";

    private static final String deleteSigPid = "DELETE FROM signature WHERE pid=%d";

    private static final String deleteReplyPid = "DELETE FROM reply WHERE pid=%d";

    public PostDaoImpl() {
        this.conn = DbConnection.getInstance(DbAccess.url, DbAccess.usr, DbAccess.pwd);
    }

    private Statement createStatement() throws DaoException, SQLException {
        //if (this.conn.isClosed() && this.conn.open()) {
        //    throw new DaoException("Connection to database cannot be setup.");
        //}
        Connection connection = this.conn.getConnection();
        if (connection == null) {
            throw new DaoException("Connection setup successfully, but we " +
                    "got a null connection instance.");
        }
        return connection.createStatement();
    }

    @Override
    public void addPost(PostInfo postInfo) throws DaoException {
        try {
            Statement statement = this.createStatement();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String date = sdf.format(postInfo.getDate());
            ResultSet rs = statement.executeQuery(String.format(
                    "SELECT * FROM post WHERE pid=%d ORDER BY pid desc", postInfo.getPid()
            ));
            if (rs != null && rs.next()) {
                rs.close();
                statement.close();
                throw new PidHasExistException("Pid " + postInfo.getPid() + " is existing.");
            } else if (rs != null) {
                if (statement.executeUpdate(String.format(
                        insert, postInfo.getPid(), postInfo.getTitle(), postInfo.getContent(),
                        postInfo.getSignature(), date
                )) == 0) {
                    rs.close();
                    statement.close();
                    throw new DaoException("Insertion into database failed.");
                }
                rs.close();
                statement.close();
            } else {
                statement.close();
                throw new DaoException("Error occurs while inserting into database.");
            }
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public PostInfo getPost(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPid, pid));
            if (rs != null && rs.next()) {
                PostInfo ret = new PostInfo(
                        rs.getLong("pid"),
                        rs.getString("title"),
                        rs.getString("content"),
                        rs.getString("signature"),
                        rs.getDate("date")
                );
                rs.close();
                statement.close();
                return ret;
            } else if (rs != null) {
                rs.close();
            }
            statement.close();
            throw new DaoException("Pid " + pid + " is not exist or error occurs.");
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public void deletePost(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPid, pid));
            if (rs == null || !rs.next()) {
                if (rs != null) rs.close();
                statement.close();
                System.out.println("Pid " + pid + " is not existing.");
                throw new PidNotExistException("Pid " + pid + " is not existing.");
            }
            if (statement.executeUpdate(String.format(deleteSigPid, pid)) == 0) {
                rs.close();
                statement.close();
                System.out.println("Deletion deleteSigPid from database failed.");
                throw new DaoException("Deletion from database failed.");
            }
            /*if (statement.executeUpdate(String.format(deleteReplyPid, pid)) == 0) {
                rs.close();
                statement.close();
                System.out.println("Deletion deleteReplyPid from database failed.");
                throw new DaoException("Deletion from database failed.");
            }*/
            if (statement.executeUpdate(String.format(deletePid, pid)) == 0) {
                rs.close();
                statement.close();
                System.out.println("Deletion deletePid from database failed.");
                throw new DaoException("Deletion from database failed.");
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public long getLatestPostPid() throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery("SELECT pid FROM post ORDER BY pid desc");
            if (rs != null && rs.next()) {
                long ret = rs.getLong("pid");
                rs.close();
                statement.close();
                return ret;
            } else if (rs != null) {
                rs.close();
            }
            statement.close();
            throw new DaoException("No post is existing.");
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public boolean contain(long pid) throws DaoException {
        try {
            Statement statement = this.createStatement();
            ResultSet rs = statement.executeQuery(String.format(selectPid, pid));
            if (rs != null) {
                boolean ret = rs.next();
                rs.close();
                statement.close();
                return ret;
            }
            statement.close();
            throw new DaoException("Unknown error occurs.");
        } catch (SQLException e) {
            throw new DaoException(
                    "This Exception is caused by SQLException: " + e.getCause() +
                            ", SQL State: " + e.getSQLState() +
                            ", Error Code: " + e.getErrorCode()
            );
        }
    }

    @Override
    public Map<Long, Boolean> contain(Collection<Long> pidCollection) throws DaoException {
        Map<Long, Boolean> ret = new HashMap<>();
        for (long pid : pidCollection) {
            ret.put(pid, contain(pid));
        }
        return ret;
    }

    @Override
    public Map<Long, Boolean> contain(Map<Long, Boolean> pidMap) throws DaoException {
        return contain(pidMap.keySet());
    }
}
